package org.example.iris.mapper;

import org.apache.ibatis.annotations.*;
import org.example.iris.entity.*;

import java.util.List;

@Mapper
public interface ExperienceMapper {


    //根据经历id查找学生
    @Select("select student_id from student_desired where desired_id = #{desiredJobId}")
    Integer findStuByDesiredId(Integer desiredJobId);

    @Select("select student_id from student_education where education_id = #{educationExperienceId}")
    Integer findStuByEducationExperienceId(Integer educationExperienceId);

    @Select("select student_id from student_work where work_id = #{workExperienceId}")
    Integer findStuByWorkExperienceId(Integer workExperienceId);

    @Select("select student_id from student_project where project_id = #{projectExperienceId}")
    Integer findStuByProjectExperienceId(Integer projectExperienceId);


    //更新操作
    @Update("update desired_job set " +
            "job_position = #{jobPosition} ," +
            "job_type = #{jobType} ," +
            "job_preference = #{jobPreference} ," +
            "work_city_string = #{workCityString} ," +
            "salary_range_string = #{salaryRangeString} " +
            "where desired_job_id = #{desiredJobId}")
    void updateDesiredJob(DesiredJob desiredJob);

    @Update("update work_experience set " +
            "company_name = #{companyName} ," +
            "department_name = #{departmentName} ," +
            "job_name = #{jobName} ," +
            "job_time_range_string = #{jobTimeRangeString} ," +
            "job_content = #{jobContent}, " +
            "job_performance = #{jobPerformance} " +
            "where work_experience_id = #{workExperienceId}")
    void updateWorkExperience(WorkExperience workExperience);

    @Update("update project_experience set " +
            "project_link = #{projectLink} ," +
            "project_time_range_string = #{projectTimeRangeString} ," +
            "project_content = #{projectContent} " +
            "where project_experience_id = #{projectExperienceId}")
    void updateProjectExperience(ProjectExperience projectExperience);

    @Update("update education_experience set " +
            "school_name = #{schoolName} ," +
            "major_name = #{majorName} ," +
            "academic_structure = #{academicStructure} ," +
            "school_content = #{schoolContent} ," +
            "ranking = #{ranking} ," +
            "qualification = #{qualification} ," +
            "school_time_range_string = #{schoolTimeRangeString} " +
            "where education_experience_id = #{educationExperienceId}")
    void updateEducationExperience(EducationExperience educationExperience);


    //添加操作
    @Insert("insert into desired_job(job_position, job_type, job_preference, work_city_string, salary_range_string) " +
            "values(#{jobPosition}, #{jobType}, #{jobPreference}, #{workCityString}, #{salaryRangeString})")
    @Options(useGeneratedKeys = true, keyProperty = "desiredJobId", keyColumn = "desired_job_id")
    void addDesiredJob(DesiredJob desiredJob);

    @Insert("insert into work_experience(company_name, department_name, job_name, job_time_range_string, job_content, job_performance) " +
            "values(#{companyName}, #{departmentName}, #{jobName}, #{jobTimeRangeString}, #{jobContent}, #{jobPerformance})")
    @Options(useGeneratedKeys = true, keyProperty = "workExperienceId", keyColumn = "work_experience_id")
    void addWorkExperience(WorkExperience workExperience);

    @Insert("insert into project_experience(project_link, project_time_range_string, project_content) " +
            "values(#{projectLink}, #{projectTimeRangeString}, #{projectContent})")
    @Options(useGeneratedKeys = true, keyProperty = "projectExperienceId", keyColumn = "project_experience_id")
    void addProjectExperience(ProjectExperience projectExperience);

    @Insert("insert into education_experience(school_name, major_name, academic_structure, qualification, ranking, school_time_range_string, school_content) " +
            "values(#{schoolName}, #{majorName}, #{academicStructure}, #{qualification}, #{ranking}, #{schoolTimeRangeString}, #{schoolContent})")
    @Options(useGeneratedKeys = true, keyProperty = "educationExperienceId", keyColumn = "education_experience_id")
    void addEducationExperience(EducationExperience educationExperience);


    //添加关联表操作
    @Insert("insert into student_desired(student_id, desired_id) values(#{studentId}, #{desiredJobId})")
    void addStuDesired(Integer studentId, Integer desiredJobId);

    @Insert("insert into student_work(student_id, work_id) values(#{studentId}, #{workExperienceId})")
    void addStuWork(Integer studentId, Integer workExperienceId);

    @Insert("insert into student_project(student_id, project_id) values(#{studentId}, #{projectExperienceId})")
    void addStuProject(Integer studentId, Integer projectExperienceId);

    @Insert("insert into student_education(student_id, education_id) values(#{studentId}, #{educationExperienceId})")
    void addStuEducation(Integer studentId, Integer educationExperienceId);



    //删除操作
    @Delete("delete from desired_job where desired_job_id = #{desiredJobId}")
    void deleteDesiredJob(Integer desiredJobId);

    @Delete("delete from work_experience where work_experience_id = #{workExperienceId}")
    void deleteWorkExperience(Integer workExperienceId);

    @Delete("delete from project_experience where project_experience_id = #{projectExperienceId}")
    void deleteProjectExperience(Integer projectExperienceId);

    @Delete("delete from education_experience where education_experience_id = #{educationExperienceId}")
    void deleteEducationExperience(Integer educationExperienceId);

    //删除关联表操作
    @Delete("delete from student_desired where desired_id = #{desiredJobId}")
    void deleteStuDesired(Integer desiredJobId);

    @Delete("delete from student_work where work_id = #{workExperienceId}")
    void deleteStuWork(Integer workExperienceId);

    @Delete("delete from student_project where project_id = #{projectExperienceId}")
    void deleteStuProject(Integer projectExperienceId);

    @Delete("delete from student_education where education_id = #{educationExperienceId}")
    void deleteStuEducation(Integer educationExperienceId);

    //根据学生id查找信息
    @Select("select * from desired_job JOIN student_desired ON desired_job.desired_job_id = student_desired.desired_id " +
            "WHERE student_id = #{studentId}")
    List<DesiredJob> findDesiredJobByStuId(Integer studentId);

    @Select("select * from education_experience JOIN student_education ON education_experience.education_experience_id = student_education.education_id " +
            "WHERE student_id = #{studentId}")
    List<EducationExperience> findEducationExperienceByStuId(Integer studentId);

    @Select("select * from work_experience JOIN student_work ON work_experience.work_experience_id = student_work.work_id " +
            "WHERE student_id = #{studentId}")
    List<WorkExperience> findWorkExperienceByStuId(Integer studentId);

    @Select("select * from project_experience JOIN student_project ON project_experience.project_experience_id = student_project.project_id " +
            "WHERE student_id = #{studentId}")
    List<ProjectExperience> findProjectExperienceByStuId(Integer studentId);
}
